const { pool } = require('../config/database');

class FoodTemplate {
  // 获取所有食物模板
  static async getAllFoodTemplates() {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, icon, can_room_temp, can_refrigerated, can_frozen, 
               food_type, created_at, updated_at 
        FROM food_templates 
        ORDER BY food_type ASC, name ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取食物模板失败:', error.message);
      throw error;
    }
  }

  // 根据食物种类获取食物模板
  static async getFoodTemplatesByType(foodType) {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, icon, can_room_temp, can_refrigerated, can_frozen, 
               food_type, created_at, updated_at 
        FROM food_templates 
        WHERE food_type = ?
        ORDER BY name ASC
      `, [foodType]);
      return rows;
    } catch (error) {
      console.error('根据类型获取食物模板失败:', error.message);
      throw error;
    }
  }

  // 根据温度需求获取食物模板
  static async getFoodTemplatesByTemperature(temperatureType) {
    try {
      let whereClause = '';
      switch (temperatureType) {
        case 'room_temp':
          whereClause = 'can_room_temp = 1';
          break;
        case 'refrigerated':
          whereClause = 'can_refrigerated = 1';
          break;
        case 'frozen':
          whereClause = 'can_frozen = 1';
          break;
        default:
          throw new Error('无效的温度类型');
      }

      const [rows] = await pool.execute(`
        SELECT id, name, icon, can_room_temp, can_refrigerated, can_frozen, 
               food_type, created_at, updated_at 
        FROM food_templates 
        WHERE ${whereClause}
        ORDER BY food_type ASC, name ASC
      `);
      return rows;
    } catch (error) {
      console.error('根据温度需求获取食物模板失败:', error.message);
      throw error;
    }
  }

  // 根据ID获取食物模板详情
  static async getFoodTemplateById(id) {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, icon, can_room_temp, can_refrigerated, can_frozen, 
               food_type, created_at, updated_at 
        FROM food_templates 
        WHERE id = ?
      `, [id]);

      if (rows.length === 0) return null;
      return rows[0];
    } catch (error) {
      console.error('获取食物模板详情失败:', error.message);
      throw error;
    }
  }

  // 搜索食物模板
  static async searchFoodTemplates(keyword) {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, icon, can_room_temp, can_refrigerated, can_frozen, 
               food_type, created_at, updated_at 
        FROM food_templates 
        WHERE name LIKE ? OR food_type LIKE ?
        ORDER BY food_type ASC, name ASC
      `, [`%${keyword}%`, `%${keyword}%`]);
      return rows;
    } catch (error) {
      console.error('搜索食物模板失败:', error.message);
      throw error;
    }
  }

  // 创建食物模板
  static async createFoodTemplate(templateData) {
    try {
      const { name, icon, can_room_temp, can_refrigerated, can_frozen, food_type } = templateData;
      
      const [result] = await pool.execute(`
        INSERT INTO food_templates (name, icon, can_room_temp, can_refrigerated, can_frozen, food_type)
        VALUES (?, ?, ?, ?, ?, ?)
      `, [name, icon, can_room_temp, can_refrigerated, can_frozen, food_type]);

      return {
        id: result.insertId,
        ...templateData
      };
    } catch (error) {
      console.error('创建食物模板失败:', error.message);
      throw error;
    }
  }

  // 更新食物模板
  static async updateFoodTemplate(id, templateData) {
    try {
      const { name, icon, can_room_temp, can_refrigerated, can_frozen, food_type } = templateData;
      
      await pool.execute(`
        UPDATE food_templates 
        SET name = ?, icon = ?, can_room_temp = ?, can_refrigerated = ?, 
            can_frozen = ?, food_type = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [name, icon, can_room_temp, can_refrigerated, can_frozen, food_type, id]);

      return await this.getFoodTemplateById(id);
    } catch (error) {
      console.error('更新食物模板失败:', error.message);
      throw error;
    }
  }

  // 删除食物模板
  static async deleteFoodTemplate(id) {
    try {
      const [result] = await pool.execute(`
        DELETE FROM food_templates WHERE id = ?
      `, [id]);

      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除食物模板失败:', error.message);
      throw error;
    }
  }

  // 获取食物类型统计
  static async getFoodTypeStats() {
    try {
      const [rows] = await pool.execute(`
        SELECT 
          food_type,
          COUNT(*) as count,
          SUM(can_room_temp) as room_temp_count,
          SUM(can_refrigerated) as refrigerated_count,
          SUM(can_frozen) as frozen_count
        FROM food_templates
        GROUP BY food_type
        ORDER BY count DESC, food_type ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取食物类型统计失败:', error.message);
      throw error;
    }
  }
}

module.exports = FoodTemplate;
